﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using EnvironmentManagerSystem.MODE;
using MySql.Data.MySqlClient;
using System.Web;
namespace EnvironmentManagerSystem.BLL
{
    public class ren
    {
        #region 获取用户列表(x)
        public static DataTable GetUser(bool isAdmin, string UserName, string UserType)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(@"SELECT ID,username,logname,pwd,");
                if (isAdmin) br.Append("pwd as PasswordAdmin,");
                else br.Append(" '******'as PasswordAdmin,");
                br.Append("phone,zhineng");
                br.Append(" from tbs001_renyuan where 1=1");
                if (UserName != string.Empty)
                    br.AppendFormat(" and username like '%{0}%'", UserName);
                if (UserType != string.Empty)
                    br.AppendFormat(" and zhineng='{0}'", UserType);
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }
        #endregion

        #region 获取用户类型(x)
        public static renDataCollection GetUserType()
        {
            string strSql = "select zhineng from tbs001_renyuan WHERE zhineng IS NOT NULL AND zhineng !='' group by zhineng";
            renDataCollection list = new renDataCollection();
            using (MySqlDataReader dr = DbHelperMySQL.ExecuteReader(strSql))
            {
                while (dr.Read())
                {
                    renData model = new renData();
                    model.Zhineng = dr["zhineng"].ToString();
                    list.Add(model);
                }
            }
            return list;
        }
        #endregion

        #region 修改用户信息tbs001_renyuan、tbs001_user(x)
        //tbs001_renyuan
        public static Boolean UpdateUser(renData re)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                DataTable dt = DbHelperMySQL.Query(string.Format(" select username,logname from tbs001_renyuan where (username = '{0}' or logname='{1}') and id != {2} ", re.Username, re.Logname, re.ID1)).Tables[0];
                if (dt != null && dt.Rows.Count == 0)
                {
                    br.Append("update tbs001_renyuan set");
                    br.AppendFormat(" username ='{0}'", re.Username);
                    br.AppendFormat(",logname ='{0}'", re.Logname);
                    br.AppendFormat(",pwd ='{0}'", re.Pwd);
                    br.AppendFormat(",phone ='{0}'", re.Phone);
                    br.AppendFormat(",zhineng ='{0}'", re.Zhineng);
                    br.AppendFormat(" where ID ={0}", re.ID1);
                    return DbHelperMySQL.ExecuteSql(br.ToString()) > 0 ? true : false;
                }
                return false;
            }
            catch { return false; }
        }
        //tbs001_User
        public static Boolean UpdateUsers(string a, string b, string c, string d, string e, string f)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append("update tbs001_user set");
                br.AppendFormat(" UserName ='{0}'", a);
                br.AppendFormat(",LoginName ='{0}'", b);
                br.AppendFormat(",Password ='{0}'", c);
                br.AppendFormat(",Phone ='{0}'", d);
                br.AppendFormat(" where InUse=2 and UserName ='{0}' and LoginName='{1}'", e, f);
                return DbHelperMySQL.ExecuteSql(br.ToString()) > 0 ? true : false;
            }
            catch { return false; }
        }
        #endregion

        #region 判断用户是否存在(x)
        //判断用户是否存在
        public static bool WhetherExistence(renData rd)
        {
            DataTable dt = DbHelperMySQL.Query(string.Format(" select logname,username from tbs001_renyuan where logname = '{0}' or username = '{1}'", rd.Logname, rd.Username)).Tables[0];
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }
        #endregion

        #region 添加用户(x)
        //添加用户
        public static bool AddUser(renData rd)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" insert into tbs001_renyuan (username,logname,pwd,phone,zhineng) values ( ");
                br.AppendFormat("'{0}',", rd.Username);
                br.AppendFormat("'{0}',", rd.Logname);
                br.AppendFormat("'{0}',", rd.Pwd);
                br.AppendFormat("'{0}',", rd.Phone);
                br.AppendFormat("'{0}'", rd.Zhineng);
                br.AppendFormat(" )");
                return DbHelperMySQL.ExecuteSql(br.ToString()) > 0 ? true : false;
            }
            catch { return false; }
        }

        public static bool AddUsers(UserData ud)
        {
            try
            {
                StringBuilder dr = new StringBuilder();
                dr.Append(" insert into tbs001_user (UserName,LoginName,Password,Phone,RoleId,InUse) values ( ");
                dr.AppendFormat("'{0}',", ud.UserName);
                dr.AppendFormat("'{0}',", ud.LoginName);
                dr.AppendFormat("'{0}',", ud.Password);
                dr.AppendFormat("'{0}',", ud.Phone);
                dr.AppendFormat("'{0}',", "环境局");
                dr.AppendFormat("'{0}'", 2);
                dr.AppendFormat(" )");
                return DbHelperMySQL.ExecuteSql(dr.ToString()) > 0 ? true : false;
            }
            catch { return false; }
        }
        #endregion

        #region 删除用户数据tbs001_renyuan、tbs001_user(x)
        //tbs001_renyuan
        public static int DeleteUser(string id)
        {
            int i = 0;
            try
            {
                i = DbHelperMySQL.ExecuteSql(string.Format("delete from tbs001_renyuan where id='{0}'", id));
            }
            catch (Exception ex)
            {

            }
            return i;
        }
        //tbs001_user
        public static int DeleteUsers(string UserName, string LoginName)
        {
            int i = 0;
            try
            {
                i = DbHelperMySQL.ExecuteSql(string.Format("delete from tbs001_user where UserName='{0}' and LoginName='{1}' and InUse=2", UserName, LoginName));
            }
            catch (Exception ex)
            {

            }
            return i;
        }
        #endregion

        #region 获取职能是经办人的用户列表(x)
        public static DataTable GetList()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" select ");
                br.Append(" ID");
                br.Append(",username ");
                br.Append(" from tbs001_renyuan ");
                br.Append(" WHERE zhineng = '经办人' ");
                br.Append(" order by username ");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }
        #endregion

        #region 获取用户username,ID(x)
        public static renDataCollection GetUser(string UserName)
        {
            renDataCollection rd = new renDataCollection();
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append("SELECT Zhineng,ID FROM tbs001_renyuan");
                br.AppendFormat(" where username = '{0}' ", UserName);
                MySqlDataReader dr = DbHelperMySQL.ExecuteReader(br.ToString());
                while (dr.Read())
                {
                    renData model = new renData();
                    if (dr[0] != DBNull.Value)
                        model.Zhineng = dr[0].ToString();
                    if (dr[1] != DBNull.Value)
                        model.ID1 = Convert.ToInt32(dr[1]);
                    rd.Add(model);
                }
                return rd;
            }
            catch
            {
                return null;
            }
        }
        public static renDataCollection GetRen(string ID)
        {
            renDataCollection rd = new renDataCollection();
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append("SELECT Zhineng,ID,username,logname FROM tbs001_renyuan");
                br.AppendFormat(" where ID = {0} ", ID);
                MySqlDataReader dr = DbHelperMySQL.ExecuteReader(br.ToString());
                while (dr.Read())
                {
                    renData model = new renData();
                    if (dr[0] != DBNull.Value)
                        model.Zhineng = dr[0].ToString();
                    if (dr[1] != DBNull.Value)
                        model.ID1 = Convert.ToInt32(dr[1]);
                    if (dr[2] != DBNull.Value)
                        model.Username = dr[2].ToString();
                    if (dr[3] != DBNull.Value)
                        model.Logname = dr[3].ToString();
                    rd.Add(model);
                }
                return rd;
            }
            catch
            {
                return null;
            }
        }
        #endregion

        #region 获取角色列表(x)

        /// <summary>
        /// 获取角色列表
        /// </summary>
        /// <returns></returns>
        public static bool GetQ(int RYID, string funid)
        {
            try
            {
                bool b = false;
                string str = ren.GetRenID(RYID);
                str = str.PadLeft(str.Length + 1, '\'');
                str = str.PadRight(str.Length + 1, '\'');
                str = str.Replace(",", "\',\'");
                string strSql = string.Format(@"SELECT b.* FROM tbs001_h b,tbs001_role c WHERE b.RID=c.ID AND c.RoleName IN ({0}) AND b.FID={1}", str, funid);
                using (MySqlDataReader dr = DbHelperMySQL.ExecuteReader(strSql))
                {
                    if (dr.HasRows)
                        return true;
                    else
                        return false;
                }
            }
            catch
            {
                return false;
            }
        }
        #endregion

        #region 获取用户权限(x)
        public static string GetRenID(int RYID)
        {
            string sql = string.Format("select zhineng from tbs001_renyuan where ID='{0}'", RYID);
            string ZN = string.Empty;
            ZN = DbHelperMySQL.Query(sql).Tables[0].Rows[0]["zhineng"].ToString();
            return ZN;
        }
        #endregion

        #region MyRegion




        /// <summary>
        /// 获取用户列表
        /// </summary>

        public static DataTable GetTable()
        {
            try
            {
                StringBuilder brb = new StringBuilder();
                brb.AppendFormat(" select ID,ProjectName from tbs001_developprojectbasicinfo where reportstate=1");
                return DbHelperMySQL.Query(brb.ToString()).Tables[0];
            }
            catch { return null; }
        }


        public static DataTable GetTablebyname(string name)
        {
            try
            {
                StringBuilder brb = new StringBuilder();
                brb.AppendFormat("select ID,ProjectName from tbs001_developprojectbasicinfo where ProjectName=", name);
                return DbHelperMySQL.Query(brb.ToString()).Tables[0];
            }
            catch { return null; }
        }


        public static DataTable GetTable(string where)
        {
            try
            {
                StringBuilder brb = new StringBuilder();
                brb.AppendFormat(" select ID,ProjectName from tbs001_developprojectbasicinfo where reportstate=1", where);
                return DbHelperMySQL.Query(brb.ToString()).Tables[0];
            }
            catch { return null; }
        }







        /// <summary>
        /// 环境数据编辑
        /// </summary>
        /// <param name="CName"></param>
        /// <param name="CValue"></param>
        public static void EditTable(string CName, string CValue, string PName)
        {
            try
            {
                StringBuilder brb = new StringBuilder();
                brb.Append(" UPDATE tbs001_huanping ");
                brb.Append(" SET ");
                brb.AppendFormat(" {0}='{0}' ", CName, CValue);
                brb.AppendFormat(" where ProjectName = '{0}' ", PName);
                DbHelperMySQL.ExecuteSql(brb.ToString());
            }
            catch { }
        }
        public static bool ProOper(string Sql)
        {
            List<string> list = new List<string>();
            list.Add(Sql);
            list.Add(NoticeService.EndNotice());
            return DbHelperMySQL.ExecuteSqlTran(list) > 0;
        }
        #endregion
    }
}
